﻿using mysql;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Reflection;
using log4net;
using System.Threading;

namespace server.Tool
{
    /// <summary>
    /// MySQL数据库操作工具类
    /// </summary>
    public sealed class MySqlExecuteTools
    {
        private static readonly ILog Logger = log4net.LogManager.GetLogger("server.Tool.MySqlExecuteTools");
        private static readonly object objlock = new object();
        private const int DefaultRetryCount = 3;
        private const int DefaultRetryDelayMs = 1000;

        /// <summary>
        /// 获取查询的结果数量（带重试机制）
        /// </summary>
        public static int GetCountResult(string sql, params MySqlParameter[] commandParameters)
        {
            return ExecuteWithRetry(() =>
            {
                using (MySqlConnection SqlConn = new MySqlConnection(MySQLConnectHelp.ConnectionString))
                {
                    SqlConn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, SqlConn))
                    {
                        if (commandParameters != null)
                        {
                            cmd.Parameters.AddRange(commandParameters);
                        }

                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            int result = 0;
                            while (reader.Read())
                            {
                                if (reader.HasRows)
                                {
                                    result++;
                                }
                            }
                            return result;
                        }
                    }
                }
            });
        }

        /// <summary>
        /// 获取对象结果列表
        /// </summary>
        public static List<T> GetObjectResult<T>(string sql, params MySqlParameter[] commandParameters) where T : new()
        {
            using (MySqlConnection SqlConn = new MySqlConnection(MySQLConnectHelp.ConnectionString))
            {
                SqlConn.Open();
                List<T> result = new List<T>();
                using (MySqlCommand cmd = new MySqlCommand(sql, SqlConn))
                {
                    if (commandParameters != null)
                    {
                        cmd.Parameters.AddRange(commandParameters);
                    }

                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        int fieldCount = reader.FieldCount;
                        while (reader.Read())
                        {
                            if (reader.HasRows)
                            {
                                T t = new T();
                                for (int i = 0; i < fieldCount; i++)
                                {
                                    string propertyName = reader.GetName(i);
                                    FieldInfo info = t.GetType().GetField(propertyName);
                                    if (info != null && reader.GetValue(i) != null)
                                    {
                                        info.SetValue(t, reader.GetValue(i));
                                    }
                                }
                                result.Add(t);
                            }
                        }
                    }
                }
                return result;
            }
        }

        /// <summary>
        /// 获取单字段的值列表
        /// </summary>
        public static List<object> GetSingleFieldResult(string sql, params MySqlParameter[] commandParameters)
        {
            using (MySqlConnection SqlConn = new MySqlConnection(MySQLConnectHelp.ConnectionString))
            {
                SqlConn.Open();
                List<object> list = new List<object>();
                using (MySqlCommand cmd = new MySqlCommand(sql, SqlConn))
                {
                    if (commandParameters != null)
                    {
                        cmd.Parameters.AddRange(commandParameters);
                    }

                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        int fieldCount = reader.FieldCount;
                        while (reader.Read())
                        {
                            if (reader.HasRows)
                            {
                                for (int i = 0; i < fieldCount; i++)
                                {
                                    list.Add(reader.GetValue(i));
                                }
                            }
                        }
                    }
                }
                return list;
            }
        }

        /// <summary>
        /// 执行增删改操作（带重试机制）
        /// </summary>
        public static object AddOrUpdate(string sql)
        {
            return ExecuteWithRetry(() =>
            {
                using (MySqlConnection SqlConn = new MySqlConnection(MySQLConnectHelp.ConnectionString))
                {
                    SqlConn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, SqlConn))
                    {
                        return cmd.ExecuteScalar();
                    }
                }
            });
        }

        /// <summary>
        /// 插入数据并返回自增ID（带重试机制）
        /// </summary>
        public static long GetAddID(string sql)
        {
            return ExecuteWithRetry(() =>
            {
                using (MySqlConnection SqlConn = new MySqlConnection(MySQLConnectHelp.ConnectionString))
                {
                    SqlConn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, SqlConn))
                    {
                        cmd.ExecuteNonQuery(); // 执行插入操作
                        return cmd.LastInsertedId;
                    }
                }
            });
        }

        /// <summary>
        /// 获取多字段结果（带重试机制）
        /// </summary>
        public static List<object> GetMuchFieldResult(string sql, params MySqlParameter[] commandParameters)
        {
            return ExecuteWithRetry(() =>
            {
                using (MySqlConnection SqlConn = new MySqlConnection(MySQLConnectHelp.ConnectionString))
                {
                    SqlConn.Open();
                    List<object> result = new List<object>();
                    using (MySqlCommand cmd = new MySqlCommand(sql, SqlConn))
                    {
                        if (commandParameters != null)
                        {
                            cmd.Parameters.AddRange(commandParameters);
                        }

                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            int fieldCount = reader.FieldCount;
                            while (reader.Read())
                            {
                                if (reader.HasRows)
                                {
                                    List<object> rowList = new List<object>();
                                    for (int i = 0; i < fieldCount; i++)
                                    {
                                        rowList.Add(reader.GetValue(i));
                                    }
                                    result.Add(rowList);
                                }
                            }
                        }
                    }
                    return result;
                }
            });
        }

        /// <summary>
        /// 带重试机制的通用执行方法
        /// </summary>
        private static T ExecuteWithRetry<T>(Func<T> action)
        {
            int retryCount = DefaultRetryCount;
            int delayMilliseconds = DefaultRetryDelayMs;

            for (int attempt = 1; attempt <= retryCount; attempt++)
            {
                try
                {
                    return action();
                }
                catch (Exception e)
                {
                    Logger.ErrorFormat("数据库操作失败（尝试 {0}/{1}）：{2}\n堆栈跟踪：{3}",
                        attempt, retryCount, e.Message, e.StackTrace);

                    if (attempt == retryCount)
                    {
                        Logger.Error("数据库操作最终失败，放弃重试。");
                        throw;
                    }

                    Thread.Sleep(delayMilliseconds);
                }
            }

            throw new InvalidOperationException("重试机制不应执行到此分支");
        }
    }
}